<?php
header("Content-Type: text/html; charset=iso-8859-1");

function game_save($mysqli,$gameID,$role,$players,$rndGame,$manr) {
    if ($players != null) {
        $selected = true;
    } else {
        $selected = false;
    }
	print("<div id='gameSaveContainer'>");
    print("<form id='playersForm'>");
	print("<div id='gameSave' class='saveGame scrollbarover'>");
    TS("gridtable");
    TH("<a href=\"#\" class=\"addNewPlayer\">+</a>");
    //TH("Spieler");
	print("<th colspan=2>Spieler (letzte 4 Wochen)</th>");
	if($role == 'admin') {
		 TH("Anz.");
	}
    TH("Abs.");
    TH("Pkt.");

    $checkCount = 0;
    if ($selected) {
        foreach ($players as $value) {
            $checkCount +=1;
        }
    }
	$pos=1;
	getPlayersByGameCount($mysqli,1,$selected,$role,$pos,$players,$checkCount,$manr);
	getPlayersByGameCount($mysqli,2,$selected,$role,$pos,$players,$checkCount,$manr);
	getPlayersByGameCount($mysqli,3,$selected,$role,$pos,$players,$checkCount,$manr);
    TE();
	print("</div>");
	
	TS("gridtable");
	TRS();
	print("<td colspan='2'>");
        
        $res = $mysqli->query("select distinct g.id, g.game_name from games g order by g.id desc");
        print("<select name='game_id' size='1' id='gameSelect'>");
        print("<option value=''></option>");
            $i=1;

        while (($row = $res->fetch_assoc()) != false) {
                    if ($row['id']==$gameID || $i==$rndGame) {
                            print("<option selected value=".$row['id'].">".$row['game_name']."</option>");
                    } else {
                            print("<option value=".$row['id'].">".$row['game_name']."</option>");
                     }
                     $i+=1;
            //print("<option value=" . $row['id'] . ">" . $row['game_name'] . "</option>");
        }
            $res->close();
        print("</select>");
	print("<div id='gameselecttree' style='display:none;'>");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g where gd.game_id = g.id group by g.id, g.game_name, gd.game_id order by count desc limit 10","Top Spiele");
				print("<ul>");
				print("  <li>X01");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew from games g, games_v2 g2 where g.idNew = g2.id and g2.gameGroup = 'X01' and game_name like '101%' order by game_name","101");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew from games g, games_v2 g2 where g.idNew = g2.id and g2.gameGroup = 'X01' and game_name like '301%' order by game_name","301");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew from games g, games_v2 g2 where g.idNew = g2.id and g2.gameGroup = 'X01' and game_name like '501%' order by game_name","501");
				print("  </li>");
				print("</ul>");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew from games g, games_v2 g2 where g.idNew = g2.id and g2.gameGroup = 'Half It' order by game_name","Half It");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew from games g, games_v2 g2 where g.idNew = g2.id and g2.gameGroup = 'Count Up' order by game_name","Count Up");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew from games g, games_v2 g2 where g.idNew = g2.id and g2.gameGroup = 'Scream' order by game_name","Scream");
	print("</div>");
	TDE();
	print("<td style='vertical-align: top'>");
	print("<input type='submit' name='startgame' id='startgame' value='Start' disabled />");
    print("<input type='submit' name='saveGame' value='Speichern'/>");
	print("<input type='hidden' name='role' value='".$role."'>");
	print("<input type='hidden' name='manr' value='".$manr."'>");
	TDE();
	TRE();
	TE();
    BR();
	print("</div>");
	print("</form>");
	print("</div>");
}

function getGamesBySql($mysqli,$sql,$FolderName) {
	$res = $mysqli->query($sql);
	print("<ul>");
	print("		<li>".$FolderName);
				while (($row = $res->fetch_assoc()) != false) {
	print("		  <ul>");
	print("			<li data-game-id='".$row['id']."' data-game-id-new='".$row['idNew']."'>".$row['game_name']."</li>");
	print("		  </ul>");
				}
	print("		</li>");
	print("</ul>");
}

function getPlayersByGameCount($mysqli,$type,$selected,$role,$pos,$players,$checkCount,$manr) {
	$limit = 10;
	if ($type == 1){
		$res = $mysqli->query("select players.*,TIME_TO_SEC(TIMEDIFF( NOW(),players.heartbeat)) heartbeatdiff from players, (select sum(score_abs)/count(*) score_abs,player_id from view_score_kw where manr = " . $manr . " and year = YEAR(CURRENT_DATE()) and kw > WEEK(CURRENT_DATE())-3 and score!=-1 group by player_id ) score where players.manr = " . $manr . " and players.id = score.player_id order by score_abs desc");
	} elseif ($type == 2) {
		$res = $mysqli->query("select 
                                            players.*,TIME_TO_SEC(TIMEDIFF( NOW(),players.heartbeat)) heartbeatdiff 
                                        from 
                                            players , (select sum(score_abs)/count(*) score_abs,player_id from view_score_kw where manr = " . $manr . " and score!=-1 	group by player_id ) score 
                                        where 
											players.manr = " . $manr . " and 
                                            players.id = score.player_id 
                                        order by 
                                            score_abs desc");
	} elseif ($type == 3) {
                $res = $mysqli->query("select *, TIME_TO_SEC(TIMEDIFF( NOW(),players.heartbeat)) heartbeatdiff from players where players.manr = " . $manr . " and id not in (select distinct player_id from game_data where game_data.manr = " . $manr . " ) and id < 9999 order by firstname desc ");
        }
    while (($row = $res->fetch_assoc()) != false) {
        $resCount = $mysqli->query("select count(*) count from game_data where manr = " . $manr . " and player_id =" . $row['id'] . " and score!=-1");
        $rowCount = $resCount->fetch_assoc();
        $count = $rowCount['count'];

		if (($type == 1 && $count > $limit && $row["email"] != 'disabled') || ($type == 2 && $count <= $limit) || $type == 3){
			if ($type == 1){
				$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where manr = " . $manr . " and year = YEAR(CURRENT_DATE()) and kw > WEEK(CURRENT_DATE())-3 and player_id =" . $row['id'] . " and score!=-1");
			} else {
				$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where manr = " . $manr . " and player_id =" . $row['id'] . " and score!=-1");
			}
			$rowScoreAbs = $resScoreAbs->fetch_assoc();
			$scoreAbs = round($rowScoreAbs['score_abs'], 3);
			TRS();
			$checked = false;
			if ($selected) {
				foreach ($players as $value) {
					if ($row['id'] == $value) {
						$checked = true;
					}
				}
			}
			$wins = getWinCount($mysqli,null, $row['id'] ,$manr);
			$lost = getLostCount($mysqli,null, $row['id'] ,$manr);
			$gameCount = getGameCount($mysqli,null, $row['id'] ,$manr);
                        $winQuote = 0;
                        $lostQuote = 0;
                        if (!is_numeric($gameCount)) {
                            $winQuote = round($wins / $gameCount * 100,2);
                            $lostQuote = round($lost / $gameCount * 100,2);
                        }
			

			if($role == 'admin') {
				$titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)&#13;Anzahl Spiele: ".$gameCount."'";
			} else {
				$titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)'";
			}
			
			$titleStatistic .= " data-player-id='".$row['id']."' data-manr='".$manr."'";
			
			if (isset($row['heartbeatdiff']) && $row['heartbeatdiff'] < 30 && $role == 'admin') {
				$isOnlineClass = 'isOnline';
			} else {
				$isOnlineClass = '';
			}

			if ($checked) {
				$class = "statisticByPlayer";
				TDS("color_" . $row["id"]);
				print("<input type='checkbox' name='playerid[]' value='" . $row['id'] . "' checked='checked' />");
				TDE();
				TDS("minsize " . $isOnlineClass);
				if ($row["email"] == 'disabled') {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='color:lightgrey'>".$row['firstName'] . " " . $row['lastName']."</span>");
				} else if ($count > $limit) {
					print("<span style='font-weight:bold'>".$pos."."."</span>");
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='font-weight:bold'>".$row['firstName'] . " " . $row['lastName']."</span>");
					$pos+=1;
				} else {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print($row['firstName'] . " " . $row['lastName']." (".strval(intval($count)-10) .")");
				}
				TDE();
				if($role == 'admin') {
					TD();
					print($count);
					TDE();
				}
				TD();
				print($scoreAbs);
				TDE();
			} else {
				$class = "disabled statisticByPlayer";
				TDS("color_" . $row["id"]);
				print("<input type='checkbox' name='playerid[]' value='" . $row['id'] . "' />");
				TDE();
				TDS("disabled " . $isOnlineClass);
				if ($row["email"] == 'disabled') {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='color:lightgrey'>".$row['firstName'] . " " . $row['lastName']."</span>");
				} else if ($count > $limit) {
					print($pos.".");
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='font-weight:bold'>".$row['firstName'] . " " . $row['lastName']."</span>");
					$pos+=1;
				} else {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print($row['firstName'] . " " . $row['lastName']." (".strval(intval($count)-$limit) .")");
				}
				if($role == 'admin') {
					TDS("disabled");
					print($count);
					TDE();
				}
				TDS("disabled");
				print($scoreAbs);
				TDE();
			}
			if ($checked) {
				print("<td class='selectPoints'><select name='player_" . $row['id'] . "' size='1'>");
				for ($i = 0; $i < $checkCount; $i++) {
					print("<option value='" . $i . "'>" . $i . "</option>");
				}
				print("</select></td>");
			} else {
				print("<td class='disabled'></td>");
			}
			TRE();
		}
	}
	$res->close();
}

function array_push_assoc($array, $key, $value){
$array[$key] = $value;
return $array;
}

function games_table($mysqli, $yearkw, $playerID, $gameID,$manr) {
	$sql = "select * from players, (select sum(score_abs)/count(*) score_abs,player_id from view_score_kw where manr = " . $manr . " and year = YEAR(CURRENT_DATE()) and kw > WEEK(CURRENT_DATE())-5 and kw < WEEK(CURRENT_DATE())-1 and score!=-1 group by player_id ) score where players.id = score.player_id order by score_abs desc";
	$playersRes = $mysqli->query($sql);
	$players = array();
	while (($row = $playersRes->fetch_assoc()) != false) {
		$players = array_push_assoc($players, $row['id'], $row['score_abs']);
	}
	/*echo "<pre>";
	print_r($players);
	echo "</pre>";*/
	
    $dataFound = false;
	$sql = null;
	if ($playerID != null && $gameID != null) {
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID,gd.manr from game_data gd, games g where gd.manr = " . $manr . " and gd.game_id = " . $gameID . " and gd.player_id = " . $playerID . " and gd.game_id = g.id order by gd.id desc";
		$statisticView = true;
	} else if ($playerID != null) {
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID,gd.manr from game_data gd, games g where gd.manr = " . $manr . " and gd.player_id = " . $playerID . " and gd.game_id = g.id order by gd.id desc";
		$statisticView = true;
	} else if ($gameID != null) {
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID,gd.manr from game_data gd, games g where gd.manr = " . $manr . " and gd.game_id = " . $gameID . " and gd.game_id = g.id order by gd.id desc";
		$statisticView = false;
	} else{
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID,gd.manr from game_data gd, games g where gd.manr = " . $manr . " and CONCAT(YEAR(gd.date),WEEK(gd.date,1)) = '" . $yearkw . "' and gd.game_id = g.id order by gd.id desc";
		$statisticView = false;
	}
	$res = $mysqli->query($sql);
    while (($row = $res->fetch_assoc()) != false) {
        $dataFound = true;
    }

	print("<div id='gamesTable'>");

    if ($dataFound) {
        TS("gridtable");
        TH("Datum");
        TH("Spiel");
		TH("Level");
		TH("Platz 1");
		TH("Platz 2");
		TH("Platz 3");
		TH("Platz 4");
		TH("Platz 5");
		TH("Platz 6");
		TH("Platz 7");
		TH("Platz 8");
        //print("<th colspan='10'></th>");
        $res = $mysqli->query($sql);
        while (($row = $res->fetch_assoc()) != false) {
			$week = date('W', strtotime($row['date']));
			if (isSet($memWeek) && $memWeek != $week) {
				TRS();TD();echo "KW ".$week;TDE();TRE();
			}
            TRS();
            print("<td nowrap class='disabled deleteGame' data-manr='".$row["manr"]."' data-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."'>");
            print($row['date']);
            TDE();
            print("<td nowrap class='disabled statisticByGame' data-manr='".$row["manr"]."' data-game-id='".$row["gameID"]."' data-game-uuid='".$row["game_uuid"]."'>");
            print($row['game_name']);
            TDE();
            $res2 = $mysqli->query("select * from game_data, players where players.manr = " . $manr . " and game_data.player_id = players.id and game_data.id =" . $row["id"] . " order by score desc");
			$gameLevel = 0;
            while (($row2 = $res2->fetch_assoc()) != false) {
				if(isset($players[$row2["player_id"]])) {
					$gameLevel += $players[$row2["player_id"]];
				} else {
					$gameLevel += 0.3;
				}
			}
            TDE();
            print("<td nowrap class='disabled statisticByGame'>");
            print(round($gameLevel,1));
            TDE();
            $res2 = $mysqli->query("select * from game_data, players where players.manr = " . $manr . " and game_data.player_id = players.id and game_data.id =" . $row["id"] . " order by score desc");
            while (($row2 = $res2->fetch_assoc()) != false) {
				if($statisticView == true) {
					if ($playerID == $row2["player_id"]) {
						$class="color_".$row2["player_id"]." statisticByGameAndPlayer";
					} else {
						$class="statisticByGameAndPlayer";
					}
				} else {
					$class="color_".$row2["player_id"]." statisticByGameAndPlayer";
				}
                print("<td nowrap class='".$class."' data-player-id='".$row2["player_id"]."' data-manr='".$row["manr"]."' data-game-id='".$row["gameID"]."' data-game-uuid='".$row["game_uuid"]."'>");
				print($row2["firstName"] . " ");
				if($row["game_uuid"] != 0) {
					$maxscore = getAVGByGameUUID($mysqli,$row["game_uuid"],$row2["player_id"]);
					$topscore = getTopScoreByGameUUID($mysqli,$row["game_uuid"],$row2["player_id"]);
					if ($maxscore > 60) {
						print("<span class='red blink'> AVG[".$maxscore."]</span>");
					} 
					if ($topscore > 100) {
						print("<span class='red blink'> TOP[".$topscore."]</span>");
					} 
				} 
                TDE();
            }
			$memWeek = $week;
            TRE();
        }
		$res->close();
        TE();
    }
	print("</div>");
}
function getCountScores($mysqli,$role,$manr) {
    echo"<div class='countScores scrollbarover'>";
    $yearSelected = getSelectYear();
    $weekSelected = getSelectWeek();
    $score_multiplier = 3;
    $score = 20;
    echo '<table class="gridtable">';
    echo "<tr>";
    echo "<th>Spieler X01</th>";
    echo "<th>".$score_multiplier."x".$score."</th>";
    echo "<th>20</th>";
    echo "<th>3x19</th>";
    echo "<th>19</th>";
    echo "<th>Out</th>";
    echo "<th>Avg</th>";
    echo "</tr>";
    
    $activePlayers = getActivePlayers($mysqli);
    if (is_array($activePlayers) && count($activePlayers)>0) {
        foreach ($activePlayers as $activePlayer) {
            $playerId = $activePlayer['id'];
            $playerFirstname = $activePlayer['firstname'];
            $countThrow = getCountByPlayerGameGroup($mysqli, $weekSelected, $yearSelected, $playerId, 'X01');
            if ($countThrow > 0) {
                $countThrow1x20 = getCountByScore($mysqli,$weekSelected,$yearSelected,$playerId,20,1);
                $countThrow3x20 = getCountByScore($mysqli,$weekSelected,$yearSelected,$playerId,20,3);
                $countThrow1x19 = getCountByScore($mysqli,$weekSelected,$yearSelected,$playerId,19,1);
                $countThrow3x19 = getCountByScore($mysqli,$weekSelected,$yearSelected,$playerId,19,3);

                $checkoutRate = (getCheckoutRate ($mysqli,$weekSelected,$yearSelected,$playerId,$manr));
                $dartAvg = (get3DartAVG ($mysqli,$weekSelected,$yearSelected,$playerId,$manr));

                echo "<tr>";
                echo "<td class=\"color_$playerId\">".$playerFirstname."</td>";
                echo "<td><span class=\"smallFont\">".$countThrow3x20."<br/>".(round(division($countThrow3x20,$countThrow) *100,1))."%</span></td>";
                echo "<td><span class=\"smallFont\">".$countThrow1x20."<br/>".(round(division($countThrow1x20,$countThrow) *100,1))."%</span></td>";
                echo "<td><span class=\"smallFont\">".$countThrow3x19."<br/>".(round(division($countThrow3x19,$countThrow) *100,1))."%</span></td>";
                echo "<td><span class=\"smallFont\">".$countThrow1x19."<br/>".(round(division($countThrow1x19,$countThrow) *100,1))."%</span></td>";
                echo "<td><span class=\"smallFont\">".$checkoutRate['checkoutThrowsSuccessful']."/".$checkoutRate['checkoutThrows']."<br/>".$checkoutRate['percent']."%</span></td>";
                echo "<td><span class=\"smallFont\">".(round($dartAvg,2))."</span></td>";
                echo "</tr>";                
            }
        }
    }
    echo "</table>";
    echo "</div>";
}
function getBetsStatistics($mysqli,$role,$manr) {
    print("<div id='betsStatistics' class='betsStatistics scrollbarover'>");
	TS("gridtable");
	TH("Wettk&ouml;nig");
	TH("Anz.");
	TH("S");
	TH("V");
	TH("");

	$res = $mysqli->query("select players.firstName,allbets.bet_user_id,count(allbets.bet_user_id) betscount from 
							(select bet_id,bet_user_id,bet_player_id,winner_id 
							from bets, (select game_data.game_uuid,player_id winner_id 
									  from game_data, (select game_uuid,max(score) maxscore 
											 from game_data group by game_uuid) maxscore
									  where manr = " . $manr . " and game_data.game_uuid = maxscore.game_uuid
									   and game_data.score = maxscore.maxscore) winner
								where manr = " . $manr . " and bets.game_uuid = winner.game_uuid) allbets,players
							where manr = " . $manr . " 
							and	players.id = allbets.bet_user_id
							group by allbets.bet_user_id");
	$i = 1;
	while (($row = $res->fetch_assoc()) != false) {
		$summeWinCount = 0;
		$betsCount = $row['betscount'];
		TRS();
		TD();
			print($row['firstName']);
		TDE();
		TD();
			print($betsCount);
		TDE();
		
		$sqlWinner = "select players.firstName,allbets.bet_user_id,count(allbets.bet_user_id) betswincount from 
							(select bet_id,bet_user_id,bet_player_id,winner_id 
							from bets, (select game_data.game_uuid,player_id winner_id 
									  from game_data, (select game_uuid,max(score) maxscore 
											 from game_data group by game_uuid) maxscore
									  where manr = " . $_SESSION['manr'] . " 
									   and game_data.game_uuid = maxscore.game_uuid
									   and game_data.score = maxscore.maxscore) winner
								where manr = " . $_SESSION['manr'] . " 
								  and bets.game_uuid = winner.game_uuid) allbets,players
							where manr = " . $_SESSION['manr'] . " 
							and players.id = allbets.bet_user_id
							and allbets.winner_id = allbets.bet_player_id
							and allbets.bet_user_id = ".$row['bet_user_id'];
		$betswincount = $mysqli->query($sqlWinner)->fetch_object()->betswincount;
		$summeWinCount += $betswincount;
		TD();
			print($betswincount);
		TDE();
		$sqlLoser = "select players.firstName,allbets.bet_user_id,count(allbets.bet_user_id) betswincount from 
							(select bet_id,bet_user_id,bet_player_id,winner_id 
							from bets, (select game_data.game_uuid,player_id winner_id 
									  from game_data, (select game_uuid,min(score) maxscore 
											 from game_data group by game_uuid) maxscore
									  where manr = " . $_SESSION['manr'] . " 
									   and game_data.game_uuid = maxscore.game_uuid
									   and game_data.score = maxscore.maxscore) winner
								where manr = " . $_SESSION['manr'] . " 
								  and bets.game_uuid = winner.game_uuid) allbets,players
							where manr = " . $_SESSION['manr'] . " 
							and players.id = allbets.bet_user_id
							and allbets.winner_id = allbets.bet_player_id
							and allbets.bet_user_id = ".$row['bet_user_id'];
		$betswincount = $mysqli->query($sqlLoser)->fetch_object()->betswincount;
		$summeWinCount += $betswincount;
		TD();
			print($betswincount);
		TDE();
		TD();
			print(round($summeWinCount*100/$betsCount) . "%");
		TDE();
    }
    $res->close();

    TE();
	print("</div>");
}
function getHighFinishStatistics($mysqli,$role,$manr) {
    print("<div id='highFinish' class='countScores scrollbarover'>");
	TS("gridtable");
	TH("Highfinish");
	TH("Datum");
	TH("Spiel");
	TH("Runde");
	TH("Checkout");

	$res = $mysqli->query("select base.creation_date,base.name,base.game_uuid,base.round,base.checkout,players.firstName,base.player_id,base.game_uuid,base.game_id from
							(
							select game_score_v2.game_id,game_score_v2.creation_date,maxout.name,game_score_v2.game_uuid,game_score_v2.player_id,game_score_v2.round,sum(game_score_v2.score*game_score_v2.score_multiplier) checkout from game_score_v2,
							(
							  SELECT g.name,s.game_uuid, s.player_id, s.round
							  FROM game_score_v2 s, games_v2 g
							  WHERE s.game_id = g.id and (g.id = 11 or g.id=20) and s.score_multiplier = 2 and s.dart =2
							  group by game_uuid,player_id,round) maxout
							where game_score_v2.game_uuid = maxout.game_uuid and game_score_v2.player_id = maxout.player_id and game_score_v2.round = maxout.round
							group by game_score_v2.game_uuid,game_score_v2.player_id,game_score_v2.round
							ORDER BY checkout DESC, round
							) base,
							(
							  select * from (
							  SELECT s.game_uuid, s.player_id,max(round) maxround,sum(s.score*s.score_multiplier) sumscore
							  FROM game_score_v2 s, games_v2 g
							  WHERE s.game_id = g.id and (g.id = 11 or g.id=20) 
							  group by game_uuid,player_id
							  ) sumscore
							  where sumscore.sumscore=301 or sumscore.sumscore=501
							) ischecked, players
							where ischecked.game_uuid = base.game_uuid and ischecked.player_id = base.player_id and ischecked.maxround = base.round and players.id = base.player_id
							limit 20");
	while (($row = $res->fetch_assoc()) != false) {
		TRS();
		$class="color_".$row["player_id"];
		print("<td nowrap class='".$class."'>");
		
			print($row['firstName']);
		TDE();
		TD();
			print(date_format(new DateTime($row["creation_date"]),'d.m.Y'));
		TDE();
		print("<td nowrap class='statisticByGame' data-game-id='".$row["game_id"]."' data-game-uuid='".$row["game_uuid"]."' data-manr='".$manr."'>");
			print($row['name']." DO");
		TDE();
		TD();
			print($row['round']);
		TDE();
		
		TD();
			print($row['checkout']);
		TDE();
		
    }
    $res->close();

    TE();
	print("</div>");
}
function getRandomGame($mysqli,$role,$gameID) {

	$i = 1;
	print("<div id='randomGameTabel' class='randomGameTabel scrollbarover'>");
	TS("gridtable");
	if ($gameID == null) {
		$res = $mysqli->query("select distinct g.id, g.game_name, count(gd.id) count from game_data gd, games g where manr = " . $_SESSION['manr'] . " and gd.game_id = g.id group by g.id, g.game_name, gd.game_id ORDER BY RAND() LIMIT 1");
	} else{
		$res = $mysqli->query("select id,game_name from games where manr = " . $_SESSION['manr'] . " and id = ".$gameID);
	}
    while (($row = $res->fetch_assoc()) != false) {
		$game_id = $row['id'];
		print("<th colspan='2'>N&auml;chstes Spiel: ".$row['game_name']."</th>");
		TH("Pkt.");
    }
	$res->close();
	if($role == 'admin') {
		TH("Anz.");
	}
	TH("");

	$res = $mysqli->query("select players.id playerID,players.firstName,players.lastName,games.id,games.game_name,round(sum(game_data.score/maxscore.maxgamescore)/count(*),2) score, count(*) count from
game_data, games, players,
(select id,game_id,max(score) maxgamescore from game_data group by id) maxscore
where manr = " . $_SESSION['manr'] . " and maxscore.id = game_data.id
and game_data.game_id = games.id
and game_data.player_id = players.id
and games.id = ".$game_id."
group by players.id,players.firstName,players.lastName,games.game_name
order by score desc");
	$i = 1;
	while (($row = $res->fetch_assoc()) != false) {
		$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where manr = " . $_SESSION['manr'] . " and player_id =" . $row['playerID'] . " and score!=-1");
        $rowScoreAbs = $resScoreAbs->fetch_assoc();
        $scoreAbsAll = round($rowScoreAbs['score_abs'], 3);
		if ($scoreAbsAll>$row['score']) {
			$trend = "down";
		} else {
			$trend = "up";
		}
		TRS();
		TDS("minsize");
			print($i.".");
		TDE();
		print("<td class='statisticByGameAndPlayer' data-player-id='".$row['playerID']."' data-game-id='".$game_id."'>");
			print($row['firstName']." ".$row['lastName']);
		TDE();
		TD();
			print($row['score']);
		TDE();
		TDS($trend);
		TDE();
		if($role == 'admin') {
			TD();
				print($row['count']);
			TDE();
		}
		TRE();
		$i+=1;
		if($i>10) break;
    }
    $res->close();
    TE();
	print("</div>");
	BR();
}

function getTopScores($mysqli,$role,$manr) {
        $yearSelected = getSelectYear();
        $weekSelected = getSelectWeek();
	$i = 1;
	print("<div id='topScoresTabel' class='topScoresTabel scrollbarover'>");
	TS("gridtable");
	print("<th colspan='4'>Top Scores</th>");
        if (!isTotalOverview($yearSelected.$weekSelected)) {
            $where = "							and week(creation_date) = $weekSelected 
                                                        and year(creation_date) = $yearSelected    ";
        } else {
            $where = "";
        }   
	$res = $mysqli->query("select maxscore.creation_date,games.game_name,games.id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,maxscore.maxscore maxscore
							from (select game_uuid,game_id,player_id,sum(score*score_multiplier) maxscore,creation_date 
									from game_score_v2
								   where manr = " . $manr . " 
								     and score > 0
								   group by player_id, game_uuid, round) maxscore,
							players,
							games,
							games_v2
							where players.manr = " . $manr . " 
							and players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							and games.idNew = games_v2.id
							and games_v2.gameGroup != 'Scream' 
                                                        $where 
							order by maxscore desc, week(maxscore.creation_date) desc limit 20;");
	while (($row = $res->fetch_assoc()) != false) {
		TRS();
		$class="color_".$row["player_id"];
		print("<td class='date'>");
			print(date_format(new DateTime($row["creation_date"]),'d.m.Y H:i'));
		TDE();
		print("<td nowrap class='statisticByGame' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."' data-manr='".$manr."'>");
			print($row["game_name"]);
		TDE();
		print("<td nowrap class='".$class." statisticByGameAndPlayer' data-player-id='".$row["player_id"]."' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."' data-manr='".$manr."'>");
			print($row["firstName"]);
		TDE();
		TD();
			print($row["maxscore"]);
		TDE();
		TRE();
    }
    $res->close();
    TE();
	print("</div>");
}

function getTopScore($manr) {
	global $mysqli;
	echo $sql = "select maxscore.creation_date,games.game_name,games.id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,maxscore.maxscore maxscore
							from (select game_uuid,game_id,player_id,sum(score*score_multiplier) maxscore,creation_date 
									from game_score_v2
								    where manr = " . $manr . " 
									  and score > 0
								   group by player_id, game_uuid, round) maxscore,
							players,
							games,
							games_v2
							where players.manr = " . $manr . " 
							and players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							and games.idNew = games_v2.id
							and games_v2.gameGroup != 'Scream' 
							order by maxscore desc, week(maxscore.creation_date) desc limit 1;";
	$topScoreDate = new DateTime($mysqli->query($sql)->fetch_object()->creation_date);
	$text = date_format($topScoreDate,'d.m.Y').", ".$mysqli->query($sql)->fetch_object()->firstName.", ".$mysqli->query($sql)->fetch_object()->game_name.", ".$mysqli->query($sql)->fetch_object()->maxscore;
	
	return $text;
}


function getTopAvgs($mysqli,$role,$manr) {
        $yearSelected = getSelectYear();
        $weekSelected = getSelectWeek();
	$i = 1;
	print("<div id='topAgvTabel' class='topScoresTabel scrollbarover'>");
	TS("gridtable");
	print("<th colspan='4'>Top 3-Dart-Average</th>");
        if (!isTotalOverview($yearSelected.$weekSelected)) {
            $where = "							and week(creation_date) = $weekSelected 
                                                        and year(creation_date) = $yearSelected    ";
        } else {
            $where = "";
        } 
	$sql = "select maxscore.creation_date,games.game_name,games.id,maxscore.count,maxscore.game_id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,round(maxscore.maxscore,0) maxscore
							from (select game_uuid,game_id,player_id,(sum(score*score_multiplier)/count(*))*3 maxscore,creation_date,count(*) count from game_score_v2  where manr = " . $_SESSION['manr'] . " group by player_id, game_uuid) maxscore,
							players,
							games,
							games_v2
							where players.manr = " . $manr . " 
							and players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							and games.idNew = games_v2.id
							and games_v2.gameGroup = 'X01'
                            $where                                                             
							order by maxscore desc, week(maxscore.creation_date) desc limit 20";
	$res = $mysqli->query($sql);
	while (($row = $res->fetch_assoc()) != false) {
		TRS();
		$class="color_".$row["player_id"];
		print("<td class='date'>");
			print(date_format(new DateTime($row["creation_date"]),'d.m.Y H:i'));
		TDE();
		print("<td nowrap class='statisticByGame' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."' data-manr='".$manr."'>");
			print($row["game_name"]);
		TDE();
		print("<td nowrap class='".$class." statisticByGameAndPlayer' data-player-id='".$row["player_id"]."' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."' data-manr='".$manr."'>");
			print($row["firstName"]);
		TDE();
		TD();
			print($row["maxscore"]);
		TDE();
		TRE();
    }
    $res->close();
    TE();
	print("</div>");
}

function getTopAvg() {
	global $mysqli;
	$sql = "select maxscore.creation_date,games.game_name,games.id,maxscore.count,maxscore.game_id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,round(maxscore.maxscore,0) maxscore
							from (select game_uuid,game_id,player_id,(sum(score*score_multiplier)/count(*))*3 maxscore,creation_date,count(*) count from game_score_v2 where manr = " . $_SESSION['manr'] . " group by player_id, game_uuid) maxscore,
							players,
							games,
							games_v2
							where players.manr = " . $_SESSION['manr'] . " 
							and players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							and games.idNew = games_v2.id
							and games_v2.gameGroup = 'X01'
							order by maxscore desc, week(maxscore.creation_date) desc limit 1;";
	$topScoreDate = new DateTime($mysqli->query($sql)->fetch_object()->creation_date);
	$text = date_format($topScoreDate,'d.m.Y').", ".$mysqli->query($sql)->fetch_object()->firstName.", ".$mysqli->query($sql)->fetch_object()->game_name.", ".$mysqli->query($sql)->fetch_object()->maxscore;
	
	return $text;
}

function getAVGByGameUUID($mysqli,$gameUUID,$playerID) {
	$sql = "select (sum(score*score_multiplier)/count(*))*3 maxscore,creation_date,count(*) count 
			from game_score_v2, games_v2
			where game_uuid = ".$gameUUID." and player_id = ".$playerID."
			and game_score_v2.game_id = games_v2.id
			and games_v2.gameGroup = 'X01'";
	$res = $mysqli->query($sql);
	if (($row = $res->fetch_assoc()) != false) {
		$maxScore = $row['maxscore'];
	}
	return round($maxScore,0);
}

function getTopScoreByGameUUID($mysqli,$gameUUID,$playerID) {
	$sql = "select sum(score*score_multiplier) maxscore,creation_date,count(*) count 
			from game_score_v2, games_v2
			where game_uuid = ".$gameUUID." and player_id = ".$playerID." 
			and game_score_v2.game_id = games_v2.id
			and games_v2.gameGroup != 'Scream' 
			group by round order by maxscore desc";
	$maxScore = 0;
	$res = $mysqli->query($sql);
	if (($row = $res->fetch_assoc()) != false) {
		$maxScore = $row['maxscore'];
	}
	return round($maxScore,0);
}

function games_kw_summary($mysqli, $yearkw, $yearkwselected,$manr) {

	$playersRes = $mysqli->query("select * from players, (select sum(score_abs)/count(*) score_abs,player_id from view_score_kw where manr = " . $manr . " and year = YEAR(CURRENT_DATE()) and kw > WEEK(CURRENT_DATE())-5 and kw < WEEK(CURRENT_DATE())-1 and score!=-1 group by player_id ) score where players.manr = " . $manr . " and players.id = score.player_id order by score_abs desc");
	$playerLevels = array();
	while (($rowPlayerLevel = $playersRes->fetch_assoc()) != false) {
		$playerLevels = array_push_assoc($playerLevels, $rowPlayerLevel['id'], $rowPlayerLevel['score_abs']);
	}
	/*echo "<pre>";
	print_r($playerLevels);
	echo "</pre>";*/

    $minPlayedGamesPercentage = 0.20;

    $where = "";
    if (!isTotalOverview($yearkw)) {
        $where = "where manr = " . $manr . " and yearkw=" . $yearkw;
    } else {
        $where = "where manr = " . $manr;
    }
    $res = $mysqli->query("select max(count_games) as maxCount from view_score_kw_winner " . $where);
    if (($row = $res->fetch_assoc()) != false) {
        $maxCount = $row['maxCount'];
    }
	$res->close();
    $minPlayedGames = $minPlayedGamesPercentage * $maxCount;
	
    $res = $mysqli->query("select * from view_all_kw");
	print("<div id='gamesKWSummary' class='gamesKWSummary scrollbarover'>");
    TS("gridtable");
    print("<th colspan=2>");
    print("</th>");
    //TH("Punkte (".round($minPlayedGames,0)."/".$maxCount.")");
	TH("S");
	TH("N");
	TH("Pkt.");
	TH("Lvl.");
	TH("");
	TH("");
    $count = 1;
    if (!isTotalOverview($yearkw)) {
        $where = "where manr = " . $manr . " and yearkw=" . $yearkw;
    } else {
        $where = "where manr = " . $manr;
    }
    $res = $mysqli->query("select * from view_score_kw_winner " . $where);
    $startTime = microtime(true);   
    while (($row = $res->fetch_assoc()) != false) {
		$class = "statisticByPlayer ";
        TRS("gridtable");
        if ($row['count_games'] >= $minPlayedGames) {
            if ($count == 1) {
                $class .= "gold";
            } else if ($count == 2) {
                $class .= "silver";
            } else if ($count == 3) {
                $class .= "bronze";
            } else {
                $class .= "";
            }
        } else {
            $class .= "";
        }

		$wins = getWinCount($mysqli,$yearkw, $row['player_id'] ,$manr);
		$lost = getLostCount($mysqli,$yearkw, $row['player_id'] ,$manr);
		$gameCount = getGameCount($mysqli,$yearkw, $row['player_id'] ,$manr);
		$winQuote = round($wins / $gameCount * 100,2);
		$lostQuote = round($lost / $gameCount * 100,2);
		
		$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where manr = " . $manr . " and player_id =" . $row['player_id'] . " and score!=-1");
        $rowScoreAbs = $resScoreAbs->fetch_assoc();
        $scoreAbsAll = round($rowScoreAbs['score_abs'], 3);
		$trendeDiff = round($row['score_abs']-$scoreAbsAll,2);
		if ($scoreAbsAll>$row['score_abs']) {
			$trend = "down";
		} else {
			$trend = "up";
		}
		
		$titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)<br />Anzahl Spiele: ".$gameCount."<br/>Trend: ".$trendeDiff."' data-manr='".$manr."'";
        TDS($class.' minsize');
		if ($row['count_games'] >= $minPlayedGames) {
			if($row['contraction'] != null) {
				print($count .".");
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print("<span style='font-weight:bold'>".$row['contraction']."</span>");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			} else {
				print($count .".");
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print("<span style='font-weight:bold'>".$row['firstName'] ."</span>");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			}
        } else {
			$class .= " notConsider ";
			if($row['contraction'] != null) {
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print($row['contraction'] . " (-". strval(intval($minPlayedGames)-intval($row['count_games'])+1) . ")");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			} else {
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print($row['firstName'] . " (-" . strval(intval($minPlayedGames)-intval($row['count_games'])+1) . ")");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			}
        }
        TDE();
        TDS($class);
                if (!isTotalOverview($yearkw)) {
                    $where = "where CONCAT(YEAR(gd.date),WEEK(gd.date,1)) = '" . $yearkw . "' and ";
                } else {
                    $where = "where ";
                }	
                $sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID from game_data gd, games g $where gd.game_id = g.id order by gd.id desc";
                $resGamesKW = $mysqli->query($sql);
                $sumLevelCount = 0;
                $sumLevel = 0;
                while (($rowPlayerGamesKW = $resGamesKW->fetch_assoc()) != false) {
                    $gameLevel = 0;
                    $resPlayerGameData = $mysqli->query("select * from game_data, players where game_data.manr = " . $manr . " and game_data.player_id = players.id and game_data.id =" . $rowPlayerGamesKW["id"] . " order by score desc");
                    $playerInGame = false;
                    if (!isTotalOverview($yearkw)) {
                        while (($rowPlayerGameData = $resPlayerGameData->fetch_assoc()) != false) {
                            if ($rowPlayerGameData["player_id"] == $row['player_id']) {
                                $playerInGame = true;
                            }
                        }
                        if ($playerInGame) {
                            $resPlayerGameData = $mysqli->query("select * from game_data, players where game_data.manr = " . $manr . " and game_data.player_id = players.id and game_data.id =" . $rowPlayerGamesKW["id"] . " order by score desc");
                            while (($rowPlayerGameData = $resPlayerGameData->fetch_assoc()) != false) {
                                if (isset($playerLevels[$rowPlayerGameData['player_id']])) {
                                    $gameLevel += $playerLevels[$rowPlayerGameData['player_id']];
                                } else {
                                    $gameLevel += 0.3;
                                }
                            }
                            $sumLevel += $gameLevel;
                            $sumLevelCount +=1;
                        }
                    }
                }

                $level = round($sumLevel/$sumLevelCount,2);
		if ($level == 0) { 
			$level = 0;
		}
		if ($row['count_games'] >= $minPlayedGames) {
			print(round($row['score_abs'], 3));
			TDE();
			TDS($class);
			print($level);
			TDE();
			TDS($class);
			print(round($row['score_abs']*$level,2));
			TDE();
			TDS($class." ".$trend);
		} else {
			print("<div class=\"notConsiderScore\">" . round($row['score_abs'], 3))."</div>";
			TDE();
			TDS($class);
			print($level);
			TDE();
			TDS($class);
			print(round($row['score_abs']*$level,2));
			TDE();
			TDS($class." ".$trend);
		}
        TDE();
        /*TDS($class);
        print($row['count_games']);
        TDE();*/
        TRE();
        if ($row['count_games'] >= $minPlayedGames) {
            $count+=1;
        }
    }
	$res->close();
    TE();
	print("</div>");
    BR();
}

function getPlayerLevels() {
    global $mysqli;
    $playersRes = $mysqli->query("select * from players, (select sum(score_abs)/count(*) score_abs,player_id from view_score_kw where manr = " . $_SESSION['manr'] . " and year = YEAR(CURRENT_DATE()) and kw > WEEK(CURRENT_DATE())-5 and kw < WEEK(CURRENT_DATE())-1 and score!=-1 group by player_id ) score where players.id = score.player_id order by score_abs desc");
    $playerLevels = array();
    while (($rowPlayerLevel = $playersRes->fetch_assoc()) != false) {
            $playerLevels = array_push_assoc($playerLevels, $rowPlayerLevel['id'], $rowPlayerLevel['score_abs']);
    }  
    $playersRes->close();
    return $playerLevels;
}

function getCountMaxPlayedGames($year = "all", $week = "all") {
    $where = "";
    if (!isTotalOverview($year = "all", $week = "all")) {
        $where = "where manr = " . $_SESSION['manr'] . " and yearkw=" . $yearkw;
    } else {
        $where = "where manr = " . $_SESSION['manr'];
    }
    $res = $mysqli->query("select max(count_games) as maxCount from view_score_kw_winner " . $where);
    if (($row = $res->fetch_assoc()) != false) {
        $maxCount = $row['maxCount'];
    }
    $res->close();
}

function games_summary($mysqli,$manr) {
    global $mysqli;
    $minPlayedGamesPercentage = 0.20;
    $minPlayedGames = 20;

    // get the levels of all players
    $playerLevels = getPlayerLevels();
    /*
    echo "<pre>";
    print_r(getPlayerLevels());
    echo "</pre>";
    */

    // get the count of the max played games
    //echo $maxCount = getCountMaxPlayedGames();
	
    print("<div id='gamesKWSummary' class='gamesKWSummary scrollbarover'>");
    TS("gridtable");
    print("<th colspan=2>");
    print("</th>");
    //TH("Punkte (".round($minPlayedGames,0)."/".$maxCount.")");
    TH("S");
    TH("N");
    TH("Pkt.");
    TH("Lvl.");
    TH("");
    TH("");
    $count = 1;
    /*if (!isTotalOverview($yearkw)) {
        $where = "where yearkw=" . $yearkw;
    } else {
        $where = "";
    }*/
    $res = $mysqli->query("select * from view_score_winner where manr = " . $manr);
    
    $startTime = microtime(true);   
    while (($row = $res->fetch_assoc()) != false) {
        $class = "statisticByPlayer ";
        TRS("gridtable");
        if ($row['count_games'] >= $minPlayedGames) {
            if ($count == 1) {
                $class .= "gold";
            } else if ($count == 2) {
                $class .= "silver";
            } else if ($count == 3) {
                $class .= "bronze";
            } else {
                $class .= "";
            }
        } else {
            $class .= "";
        }

        $wins = getWinCount($mysqli,"all", $row['player_id'],$manr );
        $lost = getLostCount($mysqli,"all", $row['player_id'],$manr );
        $gameCount = getGameCount($mysqli,"all", $row['player_id'],$manr );
        $winQuote = round($wins / $gameCount * 100,2);
        $lostQuote = round($lost / $gameCount * 100,2);
        
        $resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where manr = " . $manr . " and player_id =" . $row['player_id'] . " and score!=-1");
        $rowScoreAbs = $resScoreAbs->fetch_assoc();
        $scoreAbsAll = round($rowScoreAbs['score_abs'], 3);
        $trendeDiff = round($row['score_abs']-$scoreAbsAll,2);
        if ($scoreAbsAll>$row['score_abs']) {
                $trend = "down";
        } else {
                $trend = "up";
        }

        $titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)<br />Anzahl Spiele: ".$gameCount."<br/>Trend: ".$trendeDiff."'";
        TDS($class.' minsize');
        if ($row['count_games'] >= $minPlayedGames) {
            if($row['contraction'] != null) {
                    print($count .".");
                    TDE();
                    print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
                    print("<span style='font-weight:bold'>".$row['contraction']."</span>");
                    TDE();
                    TDS($class);
                    print($wins);
                    TDE();
                    TDS($class);
                    print($lost);
            } else {
                    print($count .".");
                    TDE();
                    print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
                    print("<span style='font-weight:bold'>".$row['firstName'] ."</span>");
                    TDE();
                    TDS($class);
                    print($wins);
                    TDE();
                    TDS($class);
                    print($lost);
            }
        } else {
            print("<div class=\"notConsider\">");
            if($row['contraction'] != null) {
                TDE();
                print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
                print($row['contraction'] . " (-". strval(intval($minPlayedGames)-intval($row['count_games'])+1) . ")");
                TDE();
                TDS($class);
                print($wins);
                TDE();
                TDS($class);
                print($lost);
            } else {
                TDE();
                print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
                print($row['firstName'] . " (-" . strval(intval($minPlayedGames)-intval($row['count_games'])+1) . ")");
                TDE();
                TDS($class);
                print($wins);
                TDE();
                TDS($class);
                print($lost);
            }
            print("</div>");
        }

        TDE();
        TDS($class);
        $where = "where gd.manr = " . $_SESSION['manr'] . " and ";
        /*
        if (!isTotalOverview($yearkw)) {
            $where = "CONCAT(YEAR(gd.date),WEEK(gd.date,1)) = '" . $yearkw . "' and ";
        } else {
            $where = "";
        }	*/
        $sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID from game_data gd, games g $where gd.game_id = g.id order by gd.id desc";
        $resGamesKW = $mysqli->query($sql);
        $sumLevelCount = 0;
        $sumLevel = 0;
        while (($rowPlayerGamesKW = $resGamesKW->fetch_assoc()) != false) {
            $gameLevel = 0;
            $resPlayerGameData = $mysqli->query("select * from game_data, players where players.manr = " . $manr . " and game_data.player_id = players.id and game_data.id =" . $rowPlayerGamesKW["id"] . " order by score desc");
            $playerInGame = false;
            while (($rowPlayerGameData = $resPlayerGameData->fetch_assoc()) != false) {
                if ($rowPlayerGameData["player_id"] == $row['player_id']) {
                    $playerInGame = true;
                }
            }
            if ($playerInGame) {
                $resPlayerGameData = $mysqli->query("select * from game_data, players where players.manr = " . $manr . " and game_data.player_id = players.id and game_data.id =" . $rowPlayerGamesKW["id"] . " order by score desc");
                while (($rowPlayerGameData = $resPlayerGameData->fetch_assoc()) != false) {
                    if (isset($playerLevels[$rowPlayerGameData['player_id']])) {
                        $gameLevel += $playerLevels[$rowPlayerGameData['player_id']];
                    } else {
                        $gameLevel += 0.3;
                    }
                }
                $sumLevel += $gameLevel;
                $sumLevelCount +=1;
            }
        }

        $level = round($sumLevel/$sumLevelCount,2);
        if ($level == 0) { 
            $level = 0;
        }
        if ($row['count_games'] >= $minPlayedGames) {
            print(round($row['score_abs'], 3));
            TDE();
            TDS($class);
            print($level);
            TDE();
            TDS($class);
            print(round($row['score_abs']*$level,2));
            TDE();
            TDS($class." ".$trend);
        } else {
            print("<div class=\"notConsiderScore\">" . round($row['score_abs'], 3))."</div>";
            TDE();
            TDS($class);
            print($level);
            TDE();
            TDS($class);
            print(round($row['score_abs']*$level,2));
            TDE();
            TDS($class." ".$trend);
        }
 
        TDE();
        /*TDS($class);
        print($row['count_games']);
        TDE();*/
        TRE();
        if ($row['count_games'] >= $minPlayedGames) {
            $count+=1;
        }
    }
    $res->close();
    TE();
	print("</div>");
    BR();
}

function getWinCount($mysqli,$yearkw, $player_id ,$manr){
	if($yearkw == null || $yearkw == "all") {
		$sql = "select count(*) count from view_games_data a where score = (select max(score) maxscore from view_games_data where manr = " . $manr . " and a.id=id) and player_id = ".$player_id;
	} else {
		$sql = "select count(*) count from view_games_data a where manr = " . $manr . " and CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and score = (select max(score) maxscore from view_games_data where manr = " . $manr . " and CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and a.id=id) and player_id = ".$player_id;
	}
	$res2 = $mysqli->query($sql);
	$row2 = $res2->fetch_assoc();
	$res2->close();
	return $row2['count'];
}
function getLostCount($mysqli,$yearkw, $player_id ,$manr){
	if($yearkw == null || $yearkw == "all") {
		$sql = "select count(*) count from view_games_data a where manr = " . $manr . " and score = 0 and player_id = ".$player_id;
	} else {
		$sql = "select count(*) count from view_games_data a where manr = " . $manr . " and CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and score = 0 and player_id = ".$player_id;
	}
	$res2 = $mysqli->query($sql);
	$row2 = $res2->fetch_assoc();
	$res2->close();
	return $row2['count'];
}
function getGameCount($mysqli,$yearkw, $player_id ,$manr){
	if($yearkw == null || $yearkw == "all") {
		$sql = "select count(*) count from view_games_data a where manr = " . $manr . " and player_id = ".$player_id ;
	} else {
		$sql = "select count(*) count from view_games_data a where manr = " . $manr . " and CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and player_id = ".$player_id ;
	}
	$res2 = $mysqli->query($sql);
	$row2 = $res2->fetch_assoc();
	$res2->close();
	return $row2['count'];
}

function BR() {
    print("<br/>");
}

function TS($class) {
    if ($class != null) {
        print("<table class='" . $class . "'>");
    } else {
        print("<table>");
    }
}

function TE() {
    print("</table>");
}

function TH($value) {
    print("<th nowrap>" . $value . "</th>");
}

function THS() {
    print("<th nowrap>");
}

function THE() {
    print("</th>");
}

function TD() {
    print("<td nowrap>");
}

function TDS($class) {
    print("<td nowrap class='" . $class . "'>");
}

function TDE() {
    print("</td>");
}

function TRS() {
    print("<tr>");
}

function TRE() {
    print("</tr>");
}

function startsWith($haystack, $needle) {
    return $needle === "" || strpos($haystack, $needle) === 0;
}

function endsWith($haystack, $needle) {
    return $needle === "" || substr($haystack, -strlen($needle)) === $needle;
}

function startDebugInfo ($debugModus) {
    $content = '';
    if ($debugModus == true) {
        $_SESSION['debugStartTime'] = microtime(true);
        $content = '<div class="debugInfo">';
    }
    return $content;
}

function endDebugInfo ($debugModus) {
    $content = '';
    if ($debugModus == true) {
        global $endTime;
        $endTime = microtime(true);   
        $diff = round($endTime - $_SESSION['debugStartTime'],2);  
        $content = 'Zeit: '.$diff.' s</div>';
    }    
    return $content;
}
function getSelectWeek() {
    if (isset($_GET['kw']) == true) {
        $yearkwselected = $_GET['kw'];
    } else {
        $yearkwselected = date('Y') . ltrim(date('W'), '0');
    }  
    return substr($yearkwselected, 4, 2) - 1;
}

function getSelectYear() {
    if (isset($_GET['kw']) == true) {
        $yearkwselected = $_GET['kw'];
    } else {
        $yearkwselected = date('Y') . ltrim(date('W'), '0');
    }
    return substr($yearkwselected, 0, 4);
}

function getCheckoutRate ($mysqli,$week,$year,$playerId,$manr) {
    $countPossibleCheckOuts = 0;
    $countSuccessfulCheckOuts = 0;
    
    $checkoutRate = array(
        "checkoutThrows"=>0,
        "checkoutThrowsSuccessful"=>0,
        "percent"=>0,
        "playerId"=>$playerId,
        "week"=>$week,
        "year"=>$year
    );
    if (!isTotalOverview($year.$week)) {
        $where = " week(game_score_v2.creation_date) = $week 
                            and year(game_score_v2.creation_date) = $year 
                            and ";
    } else {
        $where = " ";
    }       
    $res = $mysqli->query("select game_score_v2.game_uuid,games_v2.startPoints - games_v2.targetPoints  as gameScore
                            from game_score_v2  , games_v2
                            where 
                            $where 
                            games_v2.id = game_score_v2.game_id 
                            and games_v2.gameGroup = 'X01'        
                            and games_v2.outCondition = 'Double' 
                            and game_score_v2.player_id = $playerId 
                            group by game_score_v2.game_uuid 
                            order by game_score_v2.game_uuid asc");
    while (($row = $res->fetch_assoc()) != false) {
        $gameUUID = $row['game_uuid'];
        $restScore = $gamePoints = $row['gameScore'];    
        if (!isTotalOverview($year.$week)) {
            $where = "week(game_score_v2.creation_date) = $week 
                                and year(game_score_v2.creation_date) = $year 
                                and";
        } else {
            $where = "";
        }  
        $res1 = $mysqli->query("select game_score_v2.id, score,score_multiplier  
                                from game_score_v2 , games_v2 
                                where 
                                $where 
                                games_v2.id = game_score_v2.game_id 
                                and games_v2.gameGroup = 'X01' 
                                and game_score_v2.game_uuid = '$gameUUID' 
                                and game_score_v2.player_id = $playerId 
                                order by game_score_v2.id asc");      

        while (($row1 = $res1->fetch_assoc()) != false) {
            $score = $row1['score'];
            $score_multiplier = $row1['score_multiplier'];
            $sumScore = $score * $score_multiplier;
            if (($restScore % 2 == 0 && $restScore <=40) || $restScore == 50) {
                $countPossibleCheckOuts++;
            }                                        
            $restScore = $restScore - $sumScore;
            if ($restScore == 0) {
                $countSuccessfulCheckOuts++;
            }       
            $checkOutPercente = 0;
            if ($countPossibleCheckOuts > 0) {
                $checkOutPercente = round($countSuccessfulCheckOuts/$countPossibleCheckOuts*100,2);
            }
            
            $checkoutRate['checkoutThrows'] = $countPossibleCheckOuts;
            $checkoutRate['checkoutThrowsSuccessful'] = $countSuccessfulCheckOuts;
            $checkoutRate['percent'] = $checkOutPercente;

        }  
    }    
    return $checkoutRate;

}

function get3DartAVG($mysqli,$week,$year,$playerId,$manr) {
    $avg = 0;
    if (!isTotalOverview($year.$week)) {
        $where = " and week(creation_date) = $week 
                                and year(creation_date) = $year ";
    } else {
        $where = "";
    }      
    $res = $mysqli->query("select avg(sumTable.summe) as avg 
                            from 
                                (SELECT sum(score * score_multiplier) as summe 
                                FROM `game_score_v2`,`games_v2` 
                                where games_v2.gameGroup = 'X01' 
                                and game_score_v2.game_id = games_v2.id 
                                and player_id = $playerId 
                                $where 
                                group by game_uuid ,round) as sumTable");
    if (($row = $res->fetch_assoc()) != false) {
        $avg = $row['avg'];
    }
    return $avg;
}

function getCountByScore($mysqli,$week,$year,$playerId,$score,$score_multiplier) {
    $count = 0;
    if (!isTotalOverview($year.$week)) {
        $where = "week(creation_date) = $week 
                            and year(creation_date) = $year
                            and";
    } else {
        $where = "";
    }     
    $res1 = $mysqli->query("select count(*) as anzahl
                            from game_score_v2, players, games_v2 
                            where
                            $where 
                            score_multiplier = $score_multiplier    
                            and game_score_v2.player_id = players.id
                            and score = $score
                            and game_score_v2.game_id = games_v2.id  
                            and games_v2.gameGroup = 'X01'                                    
                            and players.id = $playerId");
    if (($row1 = $res1->fetch_assoc()) != false) {            
        $count = $row1['anzahl'];
    }  
    return $count;
}

function getCountByPlayerGameGroup($mysqli,$week,$year,$playerId,$gameGroup) {
    $count = 0;
    if (!isTotalOverview($year.$week)) {
        $where = " week(creation_date) = $week 
                and year(creation_date) = $year
                and ";
    } else {
        $where = "";
    }    
    $res = $mysqli->query("select count(*) as anzahl
                            from game_score_v2, players, games_v2 
                            where  
                            $where
                            game_score_v2.game_id = games_v2.id  
                            and game_score_v2.player_id = players.id 
                            and games_v2.gameGroup = '$gameGroup'                                         
                            and players.id = $playerId");
    if (($row = $res->fetch_assoc()) != false) {            
        $count = $row['anzahl'];
    }
    return $count;
}

function getActivePlayers($mysqli) {
    //$players = array(1,2,4,5,6,7,10,11,12,14,16,20,22,23,25);
    $players = array();
    $res = $mysqli->query("select id,firstname from players where manr = " . $_SESSION['manr'] . " order by id asc");
    while (($row = $res->fetch_assoc()) != false) {            
        $players[] = array("id"=>$row['id'],"firstname"=>$row['firstname']);
    }
    return $players;
}

function getPlayerNameById($mysqli,$playerId) {
    $name = "";
    $res = $mysqli->query("select firstname
                            from players 
                            where manr = " . $_SESSION['manr'] . " and id = $playerId");
    if (($row = $res->fetch_assoc()) != false) {            
        $name = $row['firstname'];
    }    
    return $name;
}

function division($divident,$divisor) {
    if ($divisor != 0) {
        return ($divident/$divisor);
    } else {
        return 0;
    }
}

function isTotalOverview ($yearkw = "all") {
    if ($yearkw == "all" || !is_numeric($yearkw)) {
        return true;
    } else {
        return false;
    }
    
}

?>